BEGIN;
SET SQL_MODE = 'ALLOW_INVALID_DATES';
DROP TABLE IF EXISTS commentarys;
DROP TABLE IF EXISTS apps;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS columns;

CREATE TABLE users
(
	userId INT UNSIGNED NOT NULL AUTO_INCREMENT,
	userName VARCHAR(20) NOT NULL,
	userMail VARCHAR(32) NOT NULL,
	userPassWordMd5 CHAR(32) NOT NULL,
	userScore INT UNSIGNED NOT NULL DEFAULT 0,
	userRegTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	userLastLoginTime TIMESTAMP NOT NULL DEFAULT 0,
	userIsAdmin BOOLEAN NOT NULL DEFAULT 0,
	PRIMARY KEY (userId),
	UNIQUE uniqueUserMail(userMail),
	INDEX indexUserMail(userMail)
)ENGINE=InnoDB AUTO_INCREMENT=0 CHARSET=UTF8;

CREATE TABLE columns
(
	columnId TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	columnName VARCHAR(20) NOT NULL,
	columnPriority TINYINT UNSIGNED NOT NULL,
	PRIMARY KEY (columnId),
	INDEX indexColumnPriority(columnPriority)
)ENGINE=InnoDB AUTO_INCREMENT=0 CHARSET=UTF8;

CREATE TABLE projects
(
	projectId INT UNSIGNED NOT NULL AUTO_INCREMENT,
	projectClick INT UNSIGNED NOT NULL DEFAULT 0,
	projectCommentary INT UNSIGNED NOT NULL DEFAULT 0,
	projectColumnId TINYINT UNSIGNED NOT NULL,
	projectIsOriginal BOOLEAN NOT NULL,
	projectTitle VARCHAR(255) NOT NULL,
	projectImageFileName VARCHAR(255) NOT NULL,
	projectContext TEXT NOT NULL,
	projectIframeUrl VARCHAR(255),
	projectOriginalUrl VARCHAR(255),
	projectCreateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	projectIsCreateApp BOOLEAN NOT NULL DEFAULT FALSE,
	PRIMARY KEY (projectId),
	FOREIGN KEY(projectColumnId) REFERENCES columns(columnId),
	INDEX indexProjectTitle(projectTitle(255)),
	INDEX indexProjectClick(projectClick),
	INDEX indexProjectCommentary(projectCommentary)
)ENGINE=InnoDB AUTO_INCREMENT=0 CHARSET=UTF8;

CREATE TABLE apps
(
	appId INT UNSIGNED NOT NULL AUTO_INCREMENT,
	appProjectId INT UNSIGNED NOT NULL,
	appUserId INT UNSIGNED NOT NULL,
	appClick INT UNSIGNED NOT NULL DEFAULT 0,
	appCommentary INT UNSIGNED NOT NULL DEFAULT 0,
	appIsPublic BOOLEAN NOT NULL DEFAULT FALSE,
	appName VARCHAR(255) NOT NULL,
	appCreateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	appContext TEXT,
	appData TEXT,
	PRIMARY KEY (appId),
	FOREIGN KEY(appProjectId) REFERENCES projects(projectId),
	FOREIGN KEY(appUserId) REFERENCES users(userId),
	INDEX indexAppUserId(appUserId),
	INDEX indexAppProjectId(appProjectId)
)ENGINE=InnoDB AUTO_INCREMENT=0 CHARSET=UTF8;

CREATE TABLE commentarys(
	commentaryId INT UNSIGNED NOT NULL AUTO_INCREMENT,
	commentaryProjectId INT UNSIGNED NOT NULL,
	commentaryAppId INT UNSIGNED NOT NULL,
	commentaryUserId INT UNSIGNED NOT NULL,
	commentaryContext VARCHAR(255) NOT NULL,
	commentaryCreateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (commentaryId),
	FOREIGN KEY(commentaryUserId) REFERENCES users(userId),
	INDEX indexCommentaryProjectId(commentaryProjectId),
	INDEX indexCommentaryAppId(commentaryAppId)
)ENGINE=InnoDB AUTO_INCREMENT=0 CHARSET=UTF8;

DELIMITER $
CREATE TRIGGER commentaryNumberCount
AFTER INSERT ON commentarys
FOR EACH ROW
BEGIN
	IF new.commentaryProjectId != 0 THEN
		UPDATE projects SET projectCommentary=projectCommentary+1 WHERE projectId=new.commentaryProjectId;
	END IF;
	IF new.commentaryAppId != 0 THEN
		UPDATE apps SET appCommentary=appCommentary+1 WHERE appId=new.commentaryAppId;
	END IF;
END $
DELIMITER ;

INSERT INTO users(userName,userMail,userPassWordMd5,userIsAdmin) VALUES ('FireCloud','849048632@qq.com','9e5dbefef7550183ebb58d5f70326101',TRUE);

INSERT INTO columns(columnName,columnPriority) VALUES
('纪念',1),
('游戏',2),
('其它',3);

INSERT INTO projects(projectColumnId,projectIsOriginal,projectTitle,projectImageFileName,projectContext,projectIframeUrl,projectOriginalUrl,projectIsCreateApp) VALUES
(2,FALSE,'一个3D的开飞机的游戏','1447740647976.png','一个3D的开飞机的游戏,点击鼠标操作','http://playcanv.as/p/JtL2iqIH','http://playcanv.as/p/JtL2iqIH',FALSE),
(3,FALSE,'3D的汽车展示，真实度惊人','1447776637876.png','3D的汽车展示，真实度惊人。','http://playcanv.as/p/RqJJ9oU9','http://playcanv.as/p/RqJJ9oU9',FALSE),
(3,TRUE,'RunJS网站Dome演示','1447829166619.png','RunJS网站Dome演示，可用RunJS网站制作作品。','1447829177493','http://localhost:8080/',FALSE),
(1,TRUE,'一个漂亮的爱情纪念','1447830285365.png','一个漂亮的爱情纪念，可设置纪念开始时间，名字等信息。','1447830292973','http://localhost:8080/',TRUE);
COMMIT
